<?php

/**
 * Class MysqlPDO 数据库类
 */

class MysqlPDO {
    private static  $dsn;
    private static $usr;
    private static $pwd;
   function __construct($db_config,$username,$password)
   {
     self::$dsn =$db_config;
     self::$usr=$username;
     self::$pwd=$password;
   }

    /**
     * 返回多行记录 
     * @param  $sql  SQL语句  格式 $sql="select * from tbl where uname=:u"
     * @param  $parameters 传值参数  ':u'=>$usrname
     * @return  记录数据数组
     */ 
    public function getQueryRows($sql, $parameters = null) {
        return $this->exeQuery($sql, $parameters);
    } 
     /**
     * 返回为单条记录 
     * @param  $sql 
     * @param  $parameters 
     * @return 返回单条记录
     */ 
    public function getQueryOneRow($sql, $parameters = null) {
        $rs = $this->exeQuery($sql, $parameters);
        if (count($rs) > 0) { 
            return $rs[0]; 
        } else { 
            return null; 
        } 
    }
    /**
     * 执行一条更新语句.insert / upadate / delete
     * @param  $sql
     * @param  $parameters
     * @return  影响行数
     */
    public function executeSql($sql, $parameters = null) {
        $conn =$this->getConnection();
        $stmt =$conn->prepare($sql);
        $stmt->execute($parameters);
        $affected_rows =$stmt->rowCount();
        $stmt = null;
        return $affected_rows;
    }

    /**
     * 查询数据[自带分页判断]
     * @param string $table
     * @param array(字段名=>array(条件=>条件值))|string $where
     * @param array(字段名=>a或者d) $order 排序方式传入d或者a，d=desc,a=asc
     * @param array(page=>1,pagesize=>20)|null $pageing
     * @return array('rows'=>rows,'data'=>data) 返回行数和数据
     */
    public function Select($table,$where,$order=null,$pageing=null){
        $list = array();
        $sql = "";
        //组合查询条件
        if ($where != null) {
            if (is_array($where)) {
                $sql .= " where ";
                $i = 1;
                foreach ($where as $key=>$value){
                    if (is_array($value)) {
                        //指定匹配方式
                        switch ($value[0]){
                            case 'in':
                                $sql .= " `$key` in (:$key)";
                                break;
                            default:
                                $sql .= " `$key` ".$value[0]." :$key";
                                break;
                        }
                    }
                    else{
                        //不指定匹配方式，默认使用等于
                        $sql .= " `$key` = :$key";
                    }
                    //未结束，条件用,分隔
                    if ($i < count($where)) {
                        $sql .= " and ";
                    }
                    $i++;
                }
            }
            else{
                $sql .= " where $where";
            }
        }
        //组合排序方式
        if ($order != null) {
            $sql .= " order by";
            $i = 1;
            foreach ($order as $key=>$value){
                if ($value == 'd')
                    $value = "desc";
                else
                    $value = "asc";
                $sql .= " `$key` $value";
                if ($i < count($order)) {
                    $sql .= ",";
                }
                $i++;
            }
        }
        //取得行数
        $rows = 0;
        $rowsql = "select count(1) as count from $table $sql";
        $stmt = $this->getConnection()->prepare($rowsql);
        //绑定参数结果
        foreach ($where as $key=>$value){
            if (is_array($value)) {
                if ($value[0] == 'like') {
                    $stmt->bindValue(":$key", '%'.$value[1].'%');
                }
                else{
                    $stmt->bindValue(":$key", $value[1]);
                }
            }
            else{
                $stmt->bindValue(":$key", $value);
            }
        }
        $stmt->execute();
        foreach ($stmt as $item){
            $rows = $item['count'];
            break;
        }
        //组合完整sql语句
        $sql = "SELECT * FROM `$table` ".$sql;

        //判断分页
        if ($pageing != null) {
            $sql .= ' limit '.(($pageing['page'] - 1)*$pageing['pagesize']).','.$pageing['pagesize'];
        }
        //预处理sql语句
        $sth = $this->getConnection()->prepare($sql);
        $sth->setFetchMode(PDO::FETCH_ASSOC);

        //绑定参数结果
        foreach ($where as $key=>$value){
            if (is_array($value)) {
                if ($value[0] == 'like') {
                    $sth->bindValue(":$key", '%'.$value[1].'%');
                }
                else{
                    $sth->bindValue(":$key", $value[1]);
                }
            }
            else{
                $sth->bindValue(":$key", $value);
            }

        }
        //取得结果
        $sth->execute();
        //将结果集转为array
        $list = $sth->fetchAll();
        //将结果转为array
        /* foreach ($sth as $row){
            $list[] = $row;
        } */
        return array("rows"=>$rows,"data"=>$list);
    }

    /**
     * 插入数据库
     * @param string $table    数据表名
     * @param array(key=>value) $fields  字段数组
     */
    public function Insert($table,$fields){
        $sql = "insert into `$table` ";
        //组合字段
        $sql .= "(";
        $i=1;
        foreach ($fields as $key=>$value){
            $sql .= "`$key`";
            if ($i < count($fields)) {
                $sql .= ",";
            }
            $i++;
        }
        $sql .= ") values (";
        //组合字段值参数
        $i=1;
        foreach ($fields as $key=>$value){
            $sql .= ":$key";
            if ($i < count($fields)) {
                $sql .= ",";
            }
            $i++;
        }
        $sql .= ")";
        //预处理sql语句
        $stmt = $this->getConnection()->prepare($sql);
        //绑定参数结果
        foreach ($fields as $key=>$value){
            $stmt->bindValue(":$key", $value);
        }
        $result = $stmt->execute();
        return $result;
    }
    /**
     * 更新数据库
     * @param string $table 数据表名称
     * @param array(字段名=字段值) $fields 字段数组
     * @param array(字段名=array(条件=>条件值)) $where  条件字段数据
     */
    public function Update($table,$fields,$where){
        $sql = "update $table set ";
        $i=1;
        foreach ($fields as $key=>$value){
            $sql .= "`$key`=:$key";
            if ($i < count($fields))
                $sql .= ',';
            $i++;
        }
        //更新条件
        if (is_array($where)) {
            $i=1;
            $sql .= ' where ';
            foreach ($where as $key=>$value){
                if (is_array($value)) {
                    if ($value[0] == 'in') {
                        $sql .= " `$key` in (".$value[1].") ";
                    }
                    else {
                        $sql .= " `$key` ".$value[0]." ".$value[1];
                    }
                }
                else{
                    $sql .= "`$key`=$value";
                }
                if ($i < count($where))
                    $sql .= ' and ';
                $i++;
            }
        }
        //预处理sql语句
        $stmt = $this->getConnection()->prepare($sql);
        file_put_contents("a.txt",$sql);
        //绑定参数结果
        foreach ($fields as $key=>$value){
            $stmt->bindValue(":$key", $value);
        }
        $result = $stmt->execute();
        return $result;
    }
    /**
     * 删除指定数据
     * @param string $table           数据表名称
     * @param array(key=value) $where 条件字段数组
     * @return boolean
     */
    public function Del($table,$where){
        $sql = "delete from $table where";
        $i=1;
        foreach ($where as $key=>$value){
            if (is_array($value)) {
                //指定匹配方式
                switch ($value[0]){
                    case 'in':
                        $sql .= " `$key` in (:$key)";
                        break;
                    default:
                        $sql .= " `$key` ".$value[0]." :$key";
                        break;
                }
            }
            else{
                //不指定匹配方式，默认使用等于
                $sql .= " `$key` = :$key";
            }
            //未结束，条件用,分隔
            if ($i < count($where)) {
                $sql .= " and ";
            }
            /* $sql .= "`$key`=:$key";
            if ($i < count($fields))
                $sql .= ' and '; */
            $i++;
        }
        //预处理sql语句
        $stmt = $this->getConnection()->prepare($sql);
file_put_contents("a.txt",$sql);
        //绑定参数结果
        foreach ($where as $key=>$value){
            //$stmt->bindValue(":$key", $value);
            if (is_array($value)) {
                if ($value[0] == 'like') {
                    $stmt->bindValue(":$key", '%'.$value[1].'%');
                }
                else{
                    $stmt->bindValue(":$key", $value[1]);
                }
            }
            else{
                $stmt->bindValue(":$key", $value);
            }
        }
        file_put_contents("b.txt",serialize($where));
        $result = $stmt->execute();
        return $result;
    }


/*  下面为私有代码段开始 */
    /**
     * 返回持久PDO连接
     * @return pdo 返回创建的PDO 持续性连接
     */
    private function getConnection() {
        try{
            $conn = new pdo(self::$dsn,self::$usr,self::$pwd,array(PDO::ATTR_PERSISTENT => true));
            $conn->setattribute(pdo::ATTR_CASE, pdo::CASE_NATURAL);
           // $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);//是否使用PHP本地模拟prepare 可以防sql注入
            $conn->query("SET NAMES 'UTF-8'");
            return $conn;
        }catch (PDOException $ex){
            exit("创建连接时出错：".$ex->getMessage());
        }
    }
    /**
     * 执行SQL查询
     * @param $sql SQL语句
     * @param null $parameters 参数方式 防注入
     * @return array 返回数组
     */
    private function exeQuery($sql, $parameters = null) {
        $conn = $this->getConnection();
        $stmt = $conn->prepare($sql);
        $stmt->execute($parameters);
        $rs = $stmt->fetchall();
        $stmt = null;
        return $rs;
    }
}
